<?php
class HomeController extends AppController {

	var $name = 'Home';
        var $uses = array();
        var $layout = "home";
        
        public function beforeFilter() {
            parent::beforeFilter();
            //$this->Auth->allow('map', 'nhd_reports', 'humanresource_reports', 'treatment_reports', 'device_reports');
        }
	
	function map()
	{
		$this->layout = "home";
		Controller::loadModel('City');
		Controller::loadModel('NhdActivityRecord');
		Controller::loadModel('NhdActivityCategory');
		Controller::loadModel('NhdActivity');
		Controller::loadModel('Treatment');
		Controller::loadModel('TreatmentRecord');
		$cities = $this->City->find('all');
		$year = $this->Session->read("c_year");
		$quarter = $this->Session->read("c_quarter");
		
		//$nd = $this->getNdDetails(array(1,2), 1, $year);
		
		foreach($cities as $c => $city)
		{
			$nd1_a = array(1,2);
			$nd = $this->getNdDetails($nd1_a, $city["City"]["id"], $year, $quarter);
			
			//dem noi dung thuc hien
			$count_nd = 0;
			//echo $nd[0]['rate'];
			foreach($nd as $iitt)
			{
				//echo $iitt['school']['rate']."__".$iitt['student']['rate'];
				if($iitt['school']['rate'] || $iitt['student']['rate']) $count_nd++;
			}
			//echo $count_nd;
			//var_dump($nd);
			if($count_nd >= 4 && $nd[0]['school']['rate'] >= 90 && $nd[1]['school']['rate'] >= 80 && $nd[2]['school']['rate'] >= 50 && $nd[3]['school']['rate'] >= 30
			   && $nd[0]['student']['rate'] >= 90 && $nd[1]['student']['rate'] >= 80 && $nd[2]['student']['rate'] >= 50 && $nd[3]['student']['rate'] >= 30)
			{
				$cities[$c]['star'] = "5";
			}
			else if($count_nd >= 4 || ($count_nd >= 3 && $nd[0]['school']['rate'] >= 80 && $nd[1]['school']['rate'] >= 60 && $nd[2]['school']['rate'] >= 30
			   && $nd[0]['student']['rate'] >= 80 && $nd[1]['student']['rate'] >= 60 && $nd[2]['student']['rate'] >= 30))
			{
				$cities[$c]['star'] = "4";
			}
			else if($count_nd >= 3 && $nd[0]['school']['rate'] >= 70 && $nd[1]['school']['rate'] >= 50 && $nd[2]['school']['rate'] >= 20
			   && $nd[0]['student']['rate'] >= 70 && $nd[1]['student']['rate'] >= 50 && $nd[2]['student']['rate'] >= 20)
			{
				$cities[$c]['star'] = "3";
			}
			else if($count_nd >= 3)
			{
				$cities[$c]['star'] = "2";
			}
			else if($count_nd >= 2)
			{
				$cities[$c]['star'] = "1";
			}
			else
			{
				$cities[$c]['star'] = "0";
			}
			
			//echo $cities[$c]['star'];
			
			//he dieu tri
			$t_count = $this->getTreatmentsRate($city["City"]["id"], $year, $quarter);
			//echo $t_count;
			if($t_count >= 20)
			{
				$cities[$c]['star_m'] = "5";
			}
			else if($t_count >= 18)
			{
				$cities[$c]['star_m'] = "4";
			}
			else if($t_count >= 16)
			{
				$cities[$c]['star_m'] = "3";
			}
			else if($t_count >= 14)
			{
				$cities[$c]['star_m'] = "2";
			}
			else if($t_count >= 12)
			{
				$cities[$c]['star_m'] = "1";
			}
			else
			{
				$cities[$c]['star_m'] = "0";
			}
		}
		
		
		
		$this->set(compact('cities'));
	}
	
	function getNdDetails($nd_array, $city_id, $year, $quarter)	
	{
		$nhdActivities = $this->NhdActivityRecord->NhdActivity->find('all');
		$nds = $this->NhdActivityCategory->find('all');
		foreach($nds as $rr => $nn)
		{
			$nds[$rr]['school']['count'] = 0;
			$nds[$rr]['school']['total'] = 0;
			$nds[$rr]['student']['count'] = 0;
			$nds[$rr]['student']['total'] = 0;
		}
		foreach($nhdActivities as $k => $item)
		{
			
			$school_record = $this->NhdActivityRecord->find("all", array(
											//'fields'=>array('SUM(NhdActivityRecord.school_rate) as sum_school_rate', 'COUNT(*) as count_school'),
											'conditions' => array(
												'NhdActivityRecord.nhd_activity_id' => $item["NhdActivity"]["id"],
												'NhdActivityRecord.school_rate IS NOT NULL',
												'NhdActivityRecord.year' => $year,
												'NhdActivityRecord.quarter' => $quarter,
												'NhdActivityRecord.city_id' => $city_id
											)
										));
			//var_dump($school_record);
			
			if(count($school_record)) {
				$activity = $this->NhdActivity->read(null, $school_record[0]['NhdActivityRecord']['nhd_activity_id']);
				//var_dump($activity);	
				foreach($nds as $rr => $nn)
				{					
					if($activity['NhdActivity']['nhd_activity_category_id'] == $nn['NhdActivityCategory']['id'])
					{						
						//echo $school_record[0]['NhdActivityRecord']['school_rate'];
						$nds[$rr]['school']['count'] += 1;
						$nds[$rr]['school']['total'] += $school_record[0]['NhdActivityRecord']['school_rate'];
					}					
				}				
			}
			
			$student_record = $this->NhdActivityRecord->find("all", array(
											//'fields'=>array('SUM(NhdActivityRecord.student_rate) as sum_student_rate', 'COUNT(*) as count_student'),
											'conditions' => array(
												'NhdActivityRecord.nhd_activity_id' => $item["NhdActivity"]["id"],
												'NhdActivityRecord.student_rate IS NOT NULL',
												'NhdActivityRecord.year' => $year,
												'NhdActivityRecord.quarter' => $quarter,
												'NhdActivityRecord.city_id' => $city_id
											)
										));
			//var_dump($student_record);
			if(count($student_record)) {
				$activity = $this->NhdActivity->read(null, $student_record[0]['NhdActivityRecord']['nhd_activity_id']);
				//var_dump($activity);	
				foreach($nds as $rr => $nn)
				{					
					if($activity['NhdActivity']['nhd_activity_category_id'] == $nn['NhdActivityCategory']['id'])
					{						
						//echo $school_record[0]['NhdActivityRecord']['school_rate'];
						$nds[$rr]['student']['count'] += 1;
						$nds[$rr]['student']['total'] += $student_record[0]['NhdActivityRecord']['student_rate'];
					}					
				}				
			}
		}
		
		$result = array();
		foreach($nds as $rr => $nn)
		{
			if($nn['NhdActivityCategory']['id'] != 2)
			{				
				$result[] = $nds[$rr];
			}
		}
		//var_dump($nds);
		$result[1]['school']['count'] += $nds[1]['school']['count'];
		$result[1]['school']['total'] += $nds[1]['school']['total'];
		$result[1]['student']['count'] += $nds[1]['student']['count'];
		$result[1]['student']['total'] += $nds[1]['student']['total'];
		//var_dump($result);
		
		//var_dump($result);	
		foreach($result as $rr => $nn)
		{
			//echo $nn['school']['count'];
			$result[$rr]['school']['rate'] = $nn['school']['count'] ? round($nn['school']['total']/$nn['school']['count'], 2) : 0;
			$result[$rr]['student']['rate'] = $nn['student']['count'] ? round($nn['student']['total']/$nn['student']['count'], 2) : 0;
		}
		
		
		
		return $result;
		
		//var_dump($nd);
	}
	
	function getTreatmentsRate($city_id, $year, $quarter)
	{
		$treatment_array = array(1,2,3,4,5,6,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23);
		$treatments = $this->Treatment->find('all', array(
							'conditions'=>array(
								'Treatment.id'=>$treatment_array
							)
						));
		
		foreach($treatments as $t => $treatment)
		{
			
			if($treatment['TreatmentCategory']['id'] == 2) {
				$count = $this->TreatmentRecord->find("all", array(
											'fields'=>array('SUM(TreatmentRecord.value) as total'),
												'conditions' => array(												
													'TreatmentRecord.treatment_id' => $treatment["Treatment"]["id"],
													'TreatmentRecord.year' => $year,
													'TreatmentRecord.quarter' => $quarter,
													'TreatmentRecord.city_id' => $city_id
												)
											));				
			}
			else
			{
				$count = $this->TreatmentRecord->find("all", array(
											'fields'=>array('SUM(TreatmentRecord.total) as total'),
												'conditions' => array(												
													'TreatmentRecord.treatment_id' => $treatment["Treatment"]["id"],
													'TreatmentRecord.year' => $year,
													'TreatmentRecord.quarter' => $quarter,
													'TreatmentRecord.city_id' => $city_id
												)
											));				
			}
			$treatments[$t]['total'] = $count[0][0]['total'] == '' ? 0 : $count[0][0]['total'];			
		}
		
		$t_count = 0;
		$caovoi = 0;
		$tram = 0;
		foreach($treatments as $t => $treatment)
		{
			if($treatment['total'])
			{
				$t_count++;
				
				//gop caovoi
				if($treatment["Treatment"]["id"] == 14)
				{
					$caovoi = 1;
				}
				if($treatment["Treatment"]["id"] == 15 && $caovoi)
				{
					$t_count--;
				}
				
				//gop tram
				if($treatment["Treatment"]["id"] == 11)
				{
					$tram = 1;
				}
				if($treatment["Treatment"]["id"] == 12 && $tram)
				{
					$t_count--;
				}
			}
		}
		return $t_count;
		//var_dump($treatments);
	}
	
	function nhd_reports()
	{
		Controller::loadModel('NhdActivityRecord');
		Controller::loadModel('NhdDeviceResource');
		$city_id = $this->Session->read("c_city_id");
		$year = $this->Session->read("c_year");
		$quarter = $this->Session->read("c_quarter");
		
		$this->layout = 'home';
		
		
		$users = $this->NhdActivityRecord->User->find('list');		
		$nhdActivities = $this->NhdActivityRecord->NhdActivity->find('all');
		foreach($nhdActivities as $k => $item)
		{			
			$school_record = $this->NhdActivityRecord->find("all", array(
											'fields'=>array('SUM(NhdActivityRecord.school_rate) as sum_school_rate', 'COUNT(*) as count_school'),
											'conditions' => array(
												'NhdActivityRecord.nhd_activity_id' => $item["NhdActivity"]["id"],
												'NhdActivityRecord.school_rate IS NOT NULL',
												'NhdActivityRecord.year' => $year,
												'NhdActivityRecord.quarter' => $quarter
											)
										));
			if($school_record[0][0]["count_school"]){
				$nhdActivities[$k]['school_rate'] = round($school_record[0][0]["sum_school_rate"]/$school_record[0][0]["count_school"], 2);
			}
			else
			{
				$nhdActivities[$k]['school_rate'] = 0;
			}
			
			$student_record = $this->NhdActivityRecord->find("all", array(
											'fields'=>array('SUM(NhdActivityRecord.student_rate) as sum_student_rate', 'COUNT(*) as count_student'),
											'conditions' => array(
												'NhdActivityRecord.nhd_activity_id' => $item["NhdActivity"]["id"],
												'NhdActivityRecord.student_rate IS NOT NULL',
												'NhdActivityRecord.year' => $year,
												'NhdActivityRecord.quarter' => $quarter
											)
										));
			if($student_record[0][0]["count_student"]){
				$nhdActivities[$k]['student_rate'] = round($student_record[0][0]["sum_student_rate"]/$student_record[0][0]["count_student"], 2);
			}
			else
			{
				$nhdActivities[$k]['student_rate'] = 0;
			}
			
			//old year
			$school_record = $this->NhdActivityRecord->find("all", array(
											'fields'=>array('SUM(NhdActivityRecord.school_rate) as sum_school_rate', 'COUNT(*) as count_school'),
											'conditions' => array(
												'NhdActivityRecord.nhd_activity_id' => $item["NhdActivity"]["id"],
												'NhdActivityRecord.school_rate IS NOT NULL',
												'NhdActivityRecord.year' => $year-1,
												'NhdActivityRecord.quarter' => $quarter
											)
										));
			if($school_record[0][0]["count_school"]){
				$nhdActivities[$k]['old']['school_rate'] = round($school_record[0][0]["sum_school_rate"]/$school_record[0][0]["count_school"], 2);
			}
			else
			{
				$nhdActivities[$k]['old']['school_rate'] = 0;
			}
			
			$student_record = $this->NhdActivityRecord->find("all", array(
											'fields'=>array('SUM(NhdActivityRecord.student_rate) as sum_student_rate', 'COUNT(*) as count_student'),
											'conditions' => array(
												'NhdActivityRecord.nhd_activity_id' => $item["NhdActivity"]["id"],
												'NhdActivityRecord.student_rate IS NOT NULL',
												'NhdActivityRecord.year' => $year-1,
												'NhdActivityRecord.quarter' => $quarter
											)
										));
			if($student_record[0][0]["count_student"]){
				$nhdActivities[$k]['old']['student_rate'] = round($student_record[0][0]["sum_student_rate"]/$student_record[0][0]["count_student"], 2);
			}
			else
			{
				$nhdActivities[$k]['old']['student_rate'] = 0;
			}	
		}
		
		$count_cities = $this->NhdActivityRecord->City->find("count");
		$cities = $this->NhdActivityRecord->City->find("all");
		
		$nd1_a = array(1,2);
		$nd1 = $this->NhdActivityRecord->City->find("all", array(
											'fields'=>array('COUNT(*) as count_city','City.id'),
											'joins' => array('INNER JOIN `hk_nhd_activity_records` AS NhdActivityRecord ON `NhdActivityRecord`.`city_id` = `City`.`id`'),
											'conditions' => array(
												'NhdActivityRecord.nhd_activity_id' => $nd1_a,
												'or'=>array('NhdActivityRecord.school_rate IS NOT NULL','NhdActivityRecord.student_rate IS NOT NULL'),
												'NhdActivityRecord.year' => $year,
												'NhdActivityRecord.quarter' => $quarter
											),
											'group'=>'City.id'
										));
		$count["nd1"] = round((count($nd1)/$count_cities)*100, 2);
		$nd1_array = array();
		foreach($nd1 as $item)
		{
			$nd1_array[]=$item["City"]["id"];
		}
				
		$nd2_a = array(3,4,5);
		$nd2 = $this->NhdActivityRecord->City->find("all", array(
											'fields'=>array('COUNT(*) as count_city','City.id'),
											'joins' => array('INNER JOIN `hk_nhd_activity_records` AS NhdActivityRecord ON `NhdActivityRecord`.`city_id` = `City`.`id`'),
											'conditions' => array(
												'NhdActivityRecord.nhd_activity_id' => $nd2_a,
												'or'=>array('NhdActivityRecord.school_rate IS NOT NULL','NhdActivityRecord.student_rate IS NOT NULL'),
												'NhdActivityRecord.year' => $year,
												'NhdActivityRecord.quarter' => $quarter
											),
											'group'=>'City.id'
										));
		
		$count["nd2"] = round((count($nd2)/$count_cities)*100, 2);	
		$nd2_array = array();
		foreach($nd2 as $item)
		{
			$nd2_array[]=$item["City"]["id"];
		}
						
		$nd3_a = array(6,7,8,9,10,11,12,13,14);
		$nd3 = $this->NhdActivityRecord->City->find("all", array(
											'fields'=>array('COUNT(*) as count_city','City.id'),
											'joins' => array('INNER JOIN `hk_nhd_activity_records` AS NhdActivityRecord ON `NhdActivityRecord`.`city_id` = `City`.`id`'),
											'conditions' => array(
												'NhdActivityRecord.nhd_activity_id' => $nd3_a,
												'or'=>array('NhdActivityRecord.school_rate IS NOT NULL','NhdActivityRecord.student_rate IS NOT NULL'),
												'NhdActivityRecord.year' => $year,
												'NhdActivityRecord.quarter' => $quarter
											),
											'group'=>'City.id'
										));
		$count["nd3"] = round((count($nd3)/$count_cities)*100, 2);
		$nd3_array = array();
		foreach($nd3 as $item)
		{
			$nd3_array[]=$item["City"]["id"];
		}

		
		$nd4_a = array(15,16);
		$nd4 = $this->NhdActivityRecord->City->find("all", array(
											'fields'=>array('COUNT(*) as count_city','City.id'),
											'joins' => array('INNER JOIN `hk_nhd_activity_records` AS NhdActivityRecord ON `NhdActivityRecord`.`city_id` = `City`.`id`'),
											'conditions' => array(
												'NhdActivityRecord.nhd_activity_id' => $nd4_a,
												'or'=>array('NhdActivityRecord.school_rate IS NOT NULL','NhdActivityRecord.student_rate IS NOT NULL'),
												'NhdActivityRecord.year' => $year,
												'NhdActivityRecord.quarter' => $quarter
											),
											'group'=>'City.id'
										));
		$count["nd4"] = round((count($nd4)/$count_cities)*100, 2);
		$nd4_array = array();
		foreach($nd4 as $item)
		{
			$nd4_array[]=$item["City"]["id"];
		}
		
		$rate["nd1"] = 0;
		$rate["nd2"] = 0;
		$rate["nd3"] = 0;
		$rate["nd4"] = 0;
		
		//Listing Devices
		$devices = $this->NhdDeviceResource->Device->find('all',array(
								  
									'fields' => array('Device.id', 'Device.name'),
									'conditions'=>array('Device.nhd'=>1)
									
								));
		foreach($cities as $key => $item)
		{
			$count_nd = 0;
			$cities[$key]["nd1"] = '';
			$cities[$key]["nd2"] = '';
			$cities[$key]["nd3"] = '';
			$cities[$key]["nd4"] = '';
			if(in_array($item["City"]["id"], $nd1_array)) { $count_nd++; $cities[$key]["nd1"] = "X"; }
			if(in_array($item["City"]["id"], $nd2_array)) { $count_nd++; $cities[$key]["nd2"] = "X"; }
			if(in_array($item["City"]["id"], $nd3_array)) { $count_nd++; $cities[$key]["nd3"] = "X"; }
			if(in_array($item["City"]["id"], $nd4_array)) { $count_nd++; $cities[$key]["nd4"] = "X"; }
			
			//trang thiet bi nhd
			
			foreach($devices as $kk => $d)
			{
				$exsit = $this->NhdDeviceResource->find("all", array(
											'fields'=>array('SUM(NhdDeviceResource.value) as devices'),
											'conditions' => array(
												'NhdDeviceResource.city_id' => $item["City"]["id"],
												'NhdDeviceResource.device_id' => $d["Device"]["id"],
												'NhdDeviceResource.year' => $year,
												'NhdDeviceResource.quarter' => $quarter
											)
										));
				if($exsit[0][0]['devices'])
					$cities[$key]['device'][$kk] = $exsit[0][0]['devices'];
				else
				{
					if($cities[$key]["nd1"] == "" && $cities[$key]["nd2"] == "" && $cities[$key]["nd3"] == "" && $cities[$key]["nd4"] == "")
						$cities[$key]['device'][$kk] = '';
					else
						$cities[$key]['device'][$kk] = '(*)';
				}
			}
			
			if($count_nd >= 1) {$rate["nd1"]++;}
			if($count_nd >= 2) {$rate["nd2"]++;}
			if($count_nd >= 3) {$rate["nd3"]++;}
			if($count_nd >= 4) {$rate["nd4"]++;}
		}
		
		$rate["nd1"] = round(($rate["nd1"]/$count_cities)*100, 2);
		$rate["nd2"] = round(($rate["nd2"]/$count_cities)*100, 2);
		$rate["nd3"] = round(($rate["nd3"]/$count_cities)*100, 2);
		$rate["nd4"] = round(($rate["nd4"]/$count_cities)*100, 2);
		//$ratez["nd1"] = $rate["nd1"]."/".$count_cities;
		//$ratez["nd2"] = $rate["nd2"]."/".$count_cities;
		//$ratez["nd3"] = $rate["nd3"]."/".$count_cities;
		//$ratez["nd4"] = $rate["nd4"]."/".$count_cities;
		
		$nd_string = '[["ND1", '.$count["nd1"].'],'.'["ND2", '.$count["nd2"].'],'.'["ND3", '.$count["nd3"].'],'.'["ND4", '.$count["nd4"].'],'.'["Không triển khai", '.($count_cities-($count["nd1"]+$count["nd2"]+$count["nd3"]+$count["nd4"])).']]';
		
		//var_dump($nd4);		
		
		$this->set(compact('nhdActivities', 'count', 'rate', 'nd_string', 'devices', 'cities'));
	}
	
	function humanresource_reports()
	{
		Controller::loadModel('HumanResource');
		Controller::loadModel('CityDetail');
		Controller::loadModel('NhdActivityRecord');
		Controller::loadModel('NhdHumanResource');
		Controller::loadModel('City');
		
		$city_id = $this->Session->read("c_city_id");
		$year = $this->Session->read("c_year");
		$quarter = $this->Session->read("c_quarter");
		
		$this->layout = 'home';
		
		//BS/Dan
		//Trung bo
		
		
		$tb = $this->getDoctorPopulation('TB',$year,$quarter);
		$tb["name"] = "Trung Bộ";
		
		$tn = $this->getDoctorPopulation('TN',$year,$quarter);
		$tn["name"] = "Tây Nguyên";		
		
		$dnb = $this->getDoctorPopulation('DNB',$year,$quarter);
		$dnb["name"] = "Đông Nam Bộ";
		
		$tnb = $this->getDoctorPopulation('TNB',$year,$quarter);
		$tnb["name"] = "Tây Nam Bộ";
		
		$tphcm = $this->getDoctorPopulation('HCM',$year,$quarter);
		$tphcm["name"] = "TP.HCM";
		
		$total = array('name'=>'Tổng');
			$total['doctors'] = 0;
			$total['doctors_nhd'] = 0;
			$total['nondoctors_nhd'] = 0;
			$total['population'] = 0;
			$total['students'] = 0;
			$total['doctor_population'] = 0;
			$total['nhd_count_employee'] = 0;
			$total['doctor_student'] = 0;
			$total['nondoctor_student'] = 0;
			
		//old year
		$tb['old'] = $this->getDoctorPopulation('TB',$year-1,$quarter);
		
		$tn['old'] = $this->getDoctorPopulation('TN',$year-1,$quarter);
		
		$dnb['old'] = $this->getDoctorPopulation('DNB',$year-1,$quarter);
		
		$tnb['old'] = $this->getDoctorPopulation('TNB',$year-1,$quarter);
		
		$tphcm['old'] = $this->getDoctorPopulation('HCM',$year-1,$quarter);
		
			$total['old']['doctors'] = 0;
			$total['old']['doctors_nhd'] = 0;
			$total['old']['nondoctors_nhd'] = 0;
			$total['old']['population'] = 0;
			$total['old']['students'] = 0;
			$total['old']['doctor_population'] = 0;
			$total['old']['nhd_count_employee'] = 0;
			$total['old']['doctor_student'] = 0;
			$total['old']['nondoctor_student'] = 0;
		
		
		
		
		$areas = array($tphcm, $tb, $tn, $dnb, $tnb);
		foreach($areas as $k => $item)
		{
			$areas[$k]['doctor_population'] = $item['doctors'] ? round($item['population']/$item['doctors'],0) : 0;
			$areas[$k]['nhd_count_employee'] = $item['doctors_nhd'] + $item['nondoctors_nhd'];
			$areas[$k]['doctor_student'] = $areas[$k]['doctors_nhd'] ? round($item['students']/$areas[$k]['doctors_nhd'],0) :0;
			$areas[$k]['nondoctor_student'] = $areas[$k]['nondoctors_nhd'] ? round($item['students']/$areas[$k]['nondoctors_nhd'],0) :0;
			$areas[$k]['doctors'] = $item['doctors'] ? $item['doctors'] : 0;
			$areas[$k]['doctors_nhd'] = $item['doctors_nhd'] ? $item['doctors_nhd'] : 0;
			$areas[$k]['nondoctors_nhd'] = $item['nondoctors_nhd'] ? $item['nondoctors_nhd'] : 0;
			
			
			$total['doctors'] += $areas[$k]['doctors'];
			$total['population'] += $areas[$k]['population'];
			$total['students'] += $areas[$k]['students'];
			$total['doctors_nhd'] += $areas[$k]['doctors_nhd'];
			$total['nondoctors_nhd'] += $areas[$k]['nondoctors_nhd'];
			
			$total['doctor_population'] = $total['doctors'] ? round($total['population']/$total['doctors'],0) : 0;
			$total['nhd_count_employee'] = $total['doctors_nhd'] + $total['nondoctors_nhd'];
			$total['doctor_student'] = $total['doctors_nhd'] ? round($total['students']/$total['doctors_nhd'],0) :0;
			$total['nondoctor_student'] = $total['nondoctors_nhd'] ? round($total['students']/$total['nondoctors_nhd'],0) :0;
			
			
			//old year
			$areas[$k]['old']['doctor_population'] = $item['old']['doctors'] ? round($item['old']['population']/$item['old']['doctors'],0) : 0;
			$areas[$k]['old']['nhd_count_employee'] = $item['old']['doctors_nhd'] + $item['old']['nondoctors_nhd'];
			$areas[$k]['old']['doctor_student'] = $areas[$k]['old']['doctors_nhd'] ? round($item['old']['students']/$areas[$k]['old']['doctors_nhd'],0) :0;
			$areas[$k]['old']['nondoctor_student'] = $areas[$k]['old']['nondoctors_nhd'] ? round($item['old']['students']/$areas[$k]['old']['nondoctors_nhd'],0) :0;
			$areas[$k]['old']['doctors'] = $item['old']['doctors'] ? $item['old']['doctors'] : 0;
			$areas[$k]['old']['doctors_nhd'] = $item['old']['doctors_nhd'] ? $item['old']['doctors_nhd'] : 0;
			$areas[$k]['old']['nondoctors_nhd'] = $item['old']['nondoctors_nhd'] ? $item['old']['nondoctors_nhd'] : 0;
			
			
			$total['old']['doctors'] += $areas[$k]['old']['doctors'];
			$total['old']['population'] += $areas[$k]['old']['population'];
			$total['old']['students'] += $areas[$k]['old']['students'];
			$total['old']['doctors_nhd'] += $areas[$k]['old']['doctors_nhd'];
			$total['old']['nondoctors_nhd'] += $areas[$k]['old']['nondoctors_nhd'];
			
			$total['old']['doctor_population'] = $total['old']['doctors'] ? round($total['old']['population']/$total['old']['doctors'],0) : 0;
			$total['old']['nhd_count_employee'] = $total['old']['doctors_nhd'] + $total['old']['nondoctors_nhd'];
			$total['old']['doctor_student'] = $total['old']['doctors_nhd'] ? round($total['old']['students']/$total['old']['doctors_nhd'],0) :0;
			$total['old']['nondoctor_student'] = $total['old']['nondoctors_nhd'] ? round($total['old']['students']/$total['old']['nondoctors_nhd'],0) :0;
			
		}
		$areas[] = $total;
		
		
		//max min
		$maxmin = $this->getMaxMinPopulation($year,$quarter);
		$maxmin['max']['city'] = $this->City->read(null, $maxmin['max']['cid']);
		$maxmin['min']['city'] = $this->City->read(null, $maxmin['min']['cid']);
		
		//old year
		$maxmin['old'] = $this->getMaxMinPopulation($year-1,$quarter);
		$maxmin['old']['max']['city'] = $this->City->read(null, $maxmin['old']['max']['cid']);
		$maxmin['old']['min']['city'] = $this->City->read(null, $maxmin['old']['min']['cid']);
		
		$this->set(compact('areas', 'tphcm', 'total', 'maxmin'));
		
		//var_dump($tphcm);
		//var_dump($tb);
	}
	
	function getDoctorPopulation($code, $year,$quarter)
	{
		$cities = $this->HumanResource->City->find('all', array(
								'fields'=>array('City.id'),
								'contain'=>false,
								'conditions'=>array(
									'City.area'=>$code,
									'City.id !='=>1
								)
							));
		//var_dump($cities);
		$city_array = array();
		if($code != 'HCM')
			foreach($cities as $city)
			{
				$city_array[] = $city["City"]["id"];
			}
		else
			$city_array[] = 1;
		//var_dump($city_array);
		
		$employee_array = array(1,2);
		$doctors = $this->HumanResource->find('all', array(
						      'fields'=>array('SUM(HumanResource.value) AS doctors'),
						      'conditions'=>array(
								'HumanResource.employee_id'=>$employee_array,
								'HumanResource.city_id'=>$city_array,
								'HumanResource.year'=>$year,
								'HumanResource.quarter'=>$quarter
						      )
						));
		
		
		//dan so tinh khu vuc
		$population = $this->CityDetail->find('all', array(
								'fields'=>array('SUM(CityDetail.population) AS population'),
								'conditions'=>array(									  
									  'CityDetail.city_id'=>$city_array,
									  'CityDetail.year'=>$year
								)
							));

		//so hoc sinh
		$nd1_a = array(1,2);
		$students = $this->NhdActivityRecord->find('all', array(
								'fields'=>array('SUM(NhdActivityRecord.student_num) AS student_num'),
								'conditions'=>array(									  
									  'NhdActivityRecord.city_id'=>$city_array,
									  'NhdActivityRecord.nhd_activity_id'=>$nd1_a,
									  'NhdActivityRecord.year'=>$year,
									  'NhdActivityRecord.quarter'=>$quarter
								)
							));
		
		//BS RHM NHD
		$employee_nhd_array = array(10,11);
		$doctors_nhd = $this->NhdHumanResource->find('all', array(
						      'fields'=>array('SUM(NhdHumanResource.value) AS doctors'),
						      'conditions'=>array(
								'NhdHumanResource.employee_id'=>$employee_nhd_array,
								'NhdHumanResource.city_id'=>$city_array,
								'NhdHumanResource.year'=>$year,
								'NhdHumanResource.quarter'=>$quarter
						      )
						));
		//BS RHM NHD
		$employee_nhd_array = array(12,13,14,15);
		$nondoctors_nhd = $this->NhdHumanResource->find('all', array(
						      'fields'=>array('SUM(NhdHumanResource.value) AS doctors'),
						      'conditions'=>array(
								'NhdHumanResource.employee_id'=>$employee_nhd_array,
								'NhdHumanResource.city_id'=>$city_array,
								'NhdHumanResource.year'=>$year,
								'NhdHumanResource.quarter'=>$quarter
						      )
						));
		
		return array('population'=>$population[0][0]["population"], 'doctors'=>$doctors[0][0]["doctors"], 'students'=>$students[0][0]["student_num"], 'doctors_nhd'=>$doctors_nhd[0][0]["doctors"], 'nondoctors_nhd'=>$nondoctors_nhd[0][0]["doctors"]);
	}
	
		
	function getMaxMinPopulation($year, $quarter)
	{
		$employee_array = array(1,2);
		$doctors = $this->HumanResource->find('all', array(
						      'fields'=>array('SUM(HumanResource.value) AS doctors','HumanResource.city_id as cid'),
						      'conditions'=>array(
								'HumanResource.employee_id'=>$employee_array,
								'HumanResource.year'=>$year,
								'HumanResource.quarter'=>$quarter
						      ),
						      'group'=>'HumanResource.city_id'
						));
		//var_dump($doctors);
		
		//dan so tinh khu vuc
		$population = $this->CityDetail->find('all', array(
								'fields'=>array('SUM(CityDetail.population) AS population','CityDetail.city_id as cid'),
								'conditions'=>array(									  
									  'CityDetail.year'=>$year
								),
								'group'=>'CityDetail.city_id'
							));
		//var_dump($population);
		
		$result['max']['doctors'] = 0;
		$result['max']['population'] = 0;
		$result['max']['cid'] = 0;
		
		$result['min']['doctors'] = 0;
		$result['min']['population'] = 0;
		$result['min']['cid'] = 0;
		
		foreach($population as $k => $item)
		{
			foreach($doctors as $m => $doc)
			{
				if($doc["HumanResource"]["cid"] == $item["CityDetail"]["cid"])
				{
					if($result['max']['population'] == 0)
					{
						$result['max']['doctors'] = $doc[0]["doctors"];
						$result['max']['population'] = $item[0]["population"];
						$result['max']['cid'] = $item["CityDetail"]["cid"];
						
						$result['min']['doctors'] = $doc[0]["doctors"];
						$result['min']['population'] = $item[0]["population"];
						$result['min']['cid'] = $item["CityDetail"]["cid"];
					}
					else
					{
						if($item[0]["population"]/$doc[0]["doctors"] > $result['max']['population']/$result['max']['doctors'])
						{
							$result['max']['doctors'] = $doc[0]["doctors"];
							$result['max']['population'] = $item[0]["population"];
							$result['max']['cid'] = $item["CityDetail"]["cid"];
						}
						if($item[0]["population"]/$doc[0]["doctors"] < $result['min']['population']/$result['min']['doctors'])
						{
							$result['min']['doctors'] = $doc[0]["doctors"];
							$result['min']['population'] = $item[0]["population"];
							$result['min']['cid'] = $item["CityDetail"]["cid"];
						}
					}
				}
			}
		}
		
		//var_dump($result);
		return $result;
	}
	
	function treatment_reports()
	{
		Controller::loadModel('TreatmentCategory');
		Controller::loadModel('TreatmentRecord');
		Controller::loadModel('HumanResource');
		Controller::loadModel('City');
		Controller::loadModel('Branch');
		$users = $this->TreatmentRecord->User->find('list');
		$this->layout = "home";
		//$city_id = $this->Session->read("c_city_id");
		$year = $this->Session->read("c_year");
		$quarter = $this->Session->read("c_quarter");
		
		$cities = $this->City->find("all");
		$branches = $this->Branch->find("all");
		
		//Listing treatments
		$treatment_categories = $this->TreatmentCategory->find("all");
		//echo count($treatment_categories);
		$treatment_catid = 2;
		$treatment_conditions = array();
		if($treatment_catid)
		{
			$treatment_conditions["treatment_category_id"] = $treatment_catid;
			
		}
		$treatments = $this->TreatmentRecord->Treatment->find('all',array(
								  
									'fields' => array('Treatment.id', 'Treatment.name', 'Treatment.unit'),
									'conditions' => $treatment_conditions,
									'order'=>'Treatment.ordering'
									
								));
		
		//create values array for rendering view
		$treatment_array = array();
		foreach($treatments as $e => $treatment)
		{
			$treatment_array[] = $treatment["Treatment"]["id"];
			$count = $this->TreatmentRecord->find("all", array(
										'fields'=>array('SUM(TreatmentRecord.value) as total'),
											'conditions' => array(												
												'TreatmentRecord.treatment_id' => $treatment["Treatment"]["id"],
												'TreatmentRecord.year' => $year,
												'TreatmentRecord.quarter' => $quarter
											)
										));
			
			$count_city = $this->TreatmentRecord->find("all", array(
										'fields'=>array('SUM(TreatmentRecord.value) as total'),
											'conditions' => array(												
												'TreatmentRecord.treatment_id' => $treatment["Treatment"]["id"],
												'TreatmentRecord.year' => $year,
												'TreatmentRecord.quarter' => $quarter,
												'total !='=>0
											),
											'group'=>'TreatmentRecord.city_id'
										));
			//echo count($count_city);
			$treatments[$e]["total"] = $count[0][0]['total'];
			$treatments[$e]["city"] = count($count_city);
			$treatments[$e]["rate"] = round((count($count_city)/count($cities))*100, 2);
		}
		
		//noi tru
		$treatment_catid = 1;
		$treatment_conditions = array();
		if($treatment_catid)
		{
			$treatment_conditions["treatment_category_id"] = $treatment_catid;
		}
		$treatments_in = $this->TreatmentRecord->Treatment->find('all',array(
								  
									'fields' => array('Treatment.id', 'Treatment.name', 'Treatment.unit'),
									'conditions' => $treatment_conditions
									
								));
		//create values array for rendering view
		foreach($treatments_in as $e => $treatment)
		{
			
			//noi tru
			$count = $this->TreatmentRecord->find("all", array(
										'fields' => array('SUM(TreatmentRecord.total) as total'),
										'conditions' => array(											
											'TreatmentRecord.treatment_id' => $treatment["Treatment"]["id"],
											'TreatmentRecord.year' => $year,
											'TreatmentRecord.quarter' => $quarter
										)
									));
			$count_city = $this->TreatmentRecord->find("all", array(
										'fields'=>array('SUM(TreatmentRecord.total) as total'),
											'conditions' => array(												
												'TreatmentRecord.treatment_id' => $treatment["Treatment"]["id"],
												'TreatmentRecord.year' => $year,
												'TreatmentRecord.quarter' => $quarter,
												'total !='=>0
											),
											'group'=>'TreatmentRecord.city_id'
										));
			//var_dump($count_city);
			$values_in['total'][$treatment["Treatment"]["id"]] = $count[0][0]["total"];
			$treatments_in[$e]["city"] = count($count_city);
			$treatments_in[$e]["rate"] = round((count($count_city)/count($cities))*100, 2);
			//$values_in['note'][$treatment["Treatment"]["id"]] = $treatmentresource_in["TreatmentRecord"]["note"];
		}
		
		//trien khai cac loai hình điều trị ngoại trú
		$count_nt = 0;
		foreach($cities as $city) {
			$tk = $this->TreatmentRecord->find("all", array(
							'fields'=>array('SUM(TreatmentRecord.value) as total', 'COUNT(TreatmentRecord.treatment_id)'),
								'conditions' => array(												
								'TreatmentRecord.treatment_id' => $treatment_array,
								'TreatmentRecord.city_id' => $city["City"]["id"],
								'TreatmentRecord.year' => $year,
								'TreatmentRecord.quarter' => $quarter,
								'total !='=>0
							),
							'group'=>'TreatmentRecord.treatment_id'
						));
			//var_dump($tk);
			if(count($tk) == count($treatments))
				$count_nt++;
		}
		$rate_nt = round(($count_nt/count($cities))*100, 2);
		
		//so huyen chua co BS RHM
		$doc_array = array(1,2);
		$count_branch = 0;
		$hr = $this->HumanResource->find("all", array(
							'fields'=>array('SUM(HumanResource.value) AS total', 'COUNT(*)'),
							'conditions' => array(						
								'HumanResource.employee_id' => $doc_array,
								'HumanResource.year' => $year,
								'HumanResource.quarter' => $quarter
							),
							'group'=>'HumanResource.branch_id'
						));
		foreach($hr as $item)
		{
			if($item[0]["total"] != 0)
				$count_branch++;
		}
		
		
		//so huyen chua co hoat dong rhm
		$count_branch_notreatment = 0;
		foreach($branches as $item)
		{
			$hr = $this->TreatmentRecord->find("all", array(
							'fields'=>array('TreatmentRecord.branch_id'),		
							'conditions' => array(
								'TreatmentRecord.branch_id'=>$item["Branch"]["id"],
								'TreatmentRecord.year' => $year,
								'TreatmentRecord.quarter' => $quarter
							)
						));
			//echo count($hr);
			if(count($hr) == 0)
				$count_branch_notreatment++;
		}
		//echo $count_branch_notreatment;
		
		//listing unit
		$this->data["TreatmentRecord"]["unit_id"] = !empty($this->data["TreatmentRecord"]["unit_id"])?$this->data["TreatmentRecord"]["unit_id"]:3;
		
		$this->set(compact('treatment_catid', 'treatment_categories', 'cities', 'branches', 'treatments', 'treatments_in', 'values','values_in', 'year', 'quarter', 'rate_nt', 'count_nt', 'count_branch', 'count_branch_notreatment', 'branches'));
	}
	
	function device_reports()
	{
		$this->layout = "home";
		Controller::loadModel('DeviceResource');
		//Controller::loadModel('DeviceResource');
		
		$cities = $this->DeviceResource->City->find('all');
		$branches = $this->DeviceResource->Branch->find('all');
		
		$city_id = $this->Session->read("c_city_id");
		$year = $this->Session->read("c_year");
		$quarter = $this->Session->read("c_quarter");
		$this->layout = "home";
		
		//Listing devices
		$devices = $this->DeviceResource->Device->find('all',array(
								  
									'fields' => array('Device.id', 'Device.name'),
									'conditions'=>array('Device.nhd !='=>1)
									
								));		
		//create values array for rendering view
		//$ghe = 0;
		//$ghemay_id = '';
		foreach($devices as $e => $device)
		{
			$deviceresource = $this->DeviceResource->find("first", array(
											'fields'=>array('SUM(DeviceResource.value) as total'),
											'conditions' => array(
												'DeviceResource.device_id' => $device["Device"]["id"],
												'DeviceResource.year' => $year,
												'DeviceResource.quarter' => $quarter
											)
										));
			$devices[$e]["total"] = $deviceresource[0]['total'];
			
			//old year
			$deviceresource = $this->DeviceResource->find("first", array(
											'fields'=>array('SUM(DeviceResource.value) as total'),
											'conditions' => array(
												'DeviceResource.device_id' => $device["Device"]["id"],
												'DeviceResource.year' => $year-1,
												'DeviceResource.quarter' => $quarter
											)
										));
			$devices[$e]['old']["total"] = $deviceresource[0]['total'];			
			
		}
		
		
		//bo ghe may tinh/thanh
		$boghemay = 0;
		foreach($branches as $branche)
		{
			$boz = $deviceresource = $this->DeviceResource->find("all", array(
											'fields'=>array('DeviceResource.value as value'),
											'conditions' => array(
												'DeviceResource.branch_id' => $branche["Branch"]["id"],
												'DeviceResource.device_id' => array(1,2),
												'DeviceResource.year' => $year,
												'DeviceResource.quarter' => $quarter
											)
										));
			//var_dump($boz);
			$min = 0;
			if(isset($boz[0]) && isset($boz[1]))
				$min = $boz[0]['DeviceResource']['value'] < $boz[1]['DeviceResource']['value'] ? $boz[0]['DeviceResource']['value'] : $boz[1]['DeviceResource']['value'];
			//echo $min;
			$boghemay += $min;
		}
		
		
		
		
		//old year
		//bo ghe may tinh/thanh
		$boghemay_old = 0;
		foreach($branches as $branche)
		{
			$boz = $deviceresource = $this->DeviceResource->find("all", array(
											'fields'=>array('DeviceResource.value as value'),
											'conditions' => array(
												'DeviceResource.branch_id' => $branche["Branch"]["id"],
												'DeviceResource.device_id' => array(1,2),
												'DeviceResource.year' => $year-1,
												'DeviceResource.quarter' => $quarter
											)
										));
			//var_dump($boz);
			$min = 0;
			if(isset($boz[0]) && isset($boz[1]))
				$min = $boz[0]['DeviceResource']['value'] < $boz[1]['DeviceResource']['value'] ? $boz[0]['DeviceResource']['value'] : $boz[1]['DeviceResource']['value'];
			//echo $min;
			$boghemay_old += $min;
		}
		
		//echo $bo/count($cities);
		$devices[] = array('Device'=>array('name'=>'<strong>Tuyến tỉnh, thành: TB</strong>'), 'total'=>round(($boghemay/count($cities)), 2), 'total_old'=>round(($boghemay_old/count($cities)), 2));
		$devices[] = array('Device'=>array('name'=>'<strong>Đơn vị RHM: TB</strong>'), 'total'=>round(($boghemay/count($branches)), 2), 'total_old'=>round(($boghemay_old/count($branches)), 2));
		
		
		$tick_a = array();
		$value_a = array();
		foreach($devices as $e => $device)
		{
			$value_a[] = $device["total"];
			$tick_a[] = $device["Device"]["name"];
		}
		$value_s = '['.implode(',', $value_a).']';
		$tick_s = '["'.implode('","', $tick_a).'"]';
		
		$this->set(compact('branches', 'devices', 'values', 'tick_s', 'value_s'));
	}
}
